#include "MySql/MySqlEmbeddedWrapper.h"

namespace mm
{

bool CreateSharedDatabase(CMySqlWrapper* pMySql)
{
    wxString Query;

    // create the database
    Query = wxString::Format("CREATE DATABASE IF NOT EXISTS `%s`", mmSharedDBName);
    if (!pMySql->RunQuery(Query) )
        return false;

    // create the users table
    Query = wxString::Format("CREATE TABLE IF NOT EXISTS `%s`.`tab_Users` (", mmSharedDBName);
    Query += "`col_FirstName` varchar(15) COLLATE latin1_general_cs NOT NULL,"
             "`col_LastName` varchar(20) COLLATE latin1_general_cs NOT NULL,"
             "`col_UserName` varchar(15) COLLATE latin1_general_cs NOT NULL,"
             "`col_Password` varchar(15) COLLATE latin1_general_cs NOT NULL,"
             "`col_DefaultDBName` varchar(20) COLLATE latin1_general_cs NOT NULL,"
             "PRIMARY KEY (`col_UserName`)"
             ") ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs";
    if (!pMySql->RunQuery(Query) )
        return false;

    // create the data table
    Query = wxString::Format("CREATE TABLE IF NOT EXISTS `%s`.`tab_Data` (", mmSharedDBName);
    Query += "`col_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,"
             "`col_TransactionType` varchar(15) COLLATE latin1_general_cs DEFAULT NULL,"
             "`col_TransactionStatus` varchar(15) COLLATE latin1_general_cs DEFAULT NULL,"
             "`col_OccuranceType` varchar(15) COLLATE latin1_general_cs DEFAULT NULL,"
             "PRIMARY KEY (`col_ID`),"
             "UNIQUE KEY `col_ID_UNIQUE` (`col_ID`)"
             ") ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs";
    if (!pMySql->RunQuery(Query) )
        return false;

    // create the categories table
    Query = wxString::Format("CREATE TABLE IF NOT EXISTS `%s`.`tab_Categories` (", mmSharedDBName);
    Query += "`col_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,"
             "`col_Category` varchar(25) COLLATE latin1_general_cs NOT NULL,"
             "`col_SubCategory` varchar(25) COLLATE latin1_general_cs DEFAULT NULL,"
             "PRIMARY KEY (`col_ID`),"
             "UNIQUE KEY `col_ID_UNIQUE` (`col_ID`)"
             ") ENGINE=InnoDB AUTO_INCREMENT=176 DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs";
    if (!pMySql->RunQuery(Query) )
        return false;

    // create the payeelist table
    Query = wxString::Format("CREATE TABLE IF NOT EXISTS `%s`.`tab_PayeeList` (", mmSharedDBName);
    Query += "`col_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,"
             "`col_Payee` varchar(25) COLLATE latin1_general_cs NOT NULL,"
             "PRIMARY KEY (`col_ID`),"
             "UNIQUE KEY `col_ID_UNIQUE` (`col_ID`)"
             ") ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs";
    if (!pMySql->RunQuery(Query) )
        return false;

    // populate persistent data in data table
    Query = wxString::Format("INSERT IGNORE INTO `%s`.`tab_Data` VALUES ", mmSharedDBName);
    Query += "(1,'Debit','Open','Once'),"
             "(2,'Deposit','Cleared','Daily'),"
             "(3,'Check','Reconciled','Weekly'),"
             "(4,'ATM',NULL,'Every 2 Weeks'),"
             "(5,'Transfer',NULL,'Twice Monthy'),"
             "(6,'Start',NULL,'Monthly'),"
             "(7,NULL,NULL,'Every 2 Months'),"
             "(8,NULL,NULL,'Every 3 Months'),"
             "(9,NULL,NULL,'Every 4 Months'),"
             "(10,NULL,NULL,'Every 6 Months'),"
             "(11,NULL,NULL,'Annually')";
    if (!pMySql->RunQuery(Query) )
        return false;

    // populate persistent data in categories table
    Query = wxString::Format("INSERT IGNORE INTO `%s`.`tab_Categories` VALUES ", mmSharedDBName);
    Query += "(1,'ATMWithdrawl',NULL),(2,'Account Transfer',NULL),"
             "(3,'Automobile','Car Payment'),(4,'Automobile','Car Wash'),"
             "(5,'Automobile','Gasoline'),(6,'Automobile','Inspection'),"
             "(7,'Automobile','Insurance'),(8,'Automobile','Maintenance'),"
             "(9,'Automobile','Parking'),(10,'Automobile','Tags'),"
             "(11,'Automobile','Toll Roads'),(12,'Bank Charges','ATM Charge'),"
             "(13,'Bank Charges','Interest Paid'),(14,'Bank Charges','Service Charge'),"
             "(15,'Bills','Cable Television'),(16,'Bills','Credit Card'),"
             "(17,'Bills','Electricity'),(18,'Bills','Garbage and Recycle'),"
             "(19,'Bills','Homeowners Dues'),(20,'Bills','Membership Fees'),"
             "(21,'Bills','Mortgage Payment'),(22,'Bills','Natural Gas'),"
             "(23,'Bills','Natural Oil'),(24,'Bills','Newspaper'),"
             "(25,'Bills','Online / Internet Service'),(26,'Bills','Rent'),"
             "(27,'Bills','Satilite Television'),(28,'Bills','Telephone'),"
             "(29,'Bills','Water and Sewer'),(30,'Bills','Cellular Phone'),"
             "(31,'Clothing',NULL),(32,'Donations','Charity'),"
             "(33,'Donations','Church'),(34,'Education','Books'),"
             "(35,'Education','Fees'),(36,'Education','Tuition'),"
             "(37,'Electronics',NULL),(38,'Entertainment','Concert'),"
             "(39,'Entertainment','Movies'),(40,'Entertainment','Movie Rental'),"
             "(41,'Entertainment','Other'),(42,'Food','Dining Out'),"
             "(43,'Food','Groceries'),(44,'Food','Ordering In'),"
             "(45,'Gifts',NULL),(46,'Healthcare','Dental'),"
             "(47,'Healthcare','Eyecare'),(48,'Healthcare','Hospital'),"
             "(49,'Healthcare','Physician'),(50,'Healthcare','Prescriptions'),"
             "(51,'Household','Furniture'),(52,'Household','House Cleaning'),"
             "(53,'Household','Yard Service'),(54,'Insurance','Automobile'),"
             "(55,'Insurance','Dental'),(56,'Insurance','Health'),"
             "(57,'Insurance','Homeowners'),(58,'Insurance','Life'),"
             "(59,'Insurance','Long Term Disability'),(60,'Insurance','Misc'),"
             "(61,'Insurance','AD&D'),(62,'Insurance','Short Term Disability'),"
             "(63,'Investment Income','Capital Gains'),(64,'Investment Income','Dividends'),"
             "(65,'Investment Income','Interest Gained'),(66,'Investment Income','Tax-Exempt Interest'),"
             "(67,'Job Expenses','Non-Reimbursed'),(68,'Job Expenses','Reimbursed'),"
             "(69,'Laundry',NULL),(70,'Leisure','Books and Magazines'),"
             "(71,'Leisure','Cultural Events'),(72,'Leisure','Entertaining'),"
             "(73,'Leisure','Movies and Video Rental'),(74,'Leisure','Photography'),"
             "(75,'Leisure','Sporting Events'),(76,'Leisure','Sporting Goods'),"
             "(77,'Leisure','Tapes and CDs'),(78,'Leisure','Toys and Games'),"
             "(79,'Loan','Bank Loan'),(80,'Loan','Student Loan'),"
             "(81,'Loan','Other Loans'),(82,'Medical','Office Visit'),"
             "(83,'Medical','Prescription Drugs'),(84,'Misc',NULL),"
             "(85,'Mortgage',NULL),(86,'Other Income','Child Support'),"
             "(87,'Other Income','Employee Stock'),(88,'Other Income','Gifts'),"
             "(89,'Other Income','Load Principal'),(90,'Other Income','Lottery'),"
             "(91,'Other Income','Tax Refund'),(92,'Other Income','Unemployment'),"
             "(93,'Personal Care','Gym Membership'),(94,'Pet Care',NULL),"
             "(95,'Retirement Income','Annuity'),(96,'Retirement Income','IRADistribution'),"
             "(97,'Retirement Income','Pension'),(98,'Retirement Income','Social Security'),"
             "(99,'Taxes','Federal Income Tax'),(100,'Taxes','Local Income Tax'),"
             "(101,'Taxes','Medicare Tax'),(102,'Taxes','Other Taxes'),"
             "(103,'Taxes','Real Estate Taxes'),(104,'Taxes','Social Security Tax'),"
             "(105,'Taxes','State Income Tax'),(106,'Utilities',NULL),"
             "(107,'Vacation','Lodging'),(108,'Vacation','Travel'),"
             "(109,'Wages and Salary','Bonus'),(110,'Wages and Salary','Commission'),"
             "(111,'Wages and Salary','Company Contributions'),(112,'Wages and Salary','Employer Matching'),"
             "(113,'Wages and Salary','Gross Pay'),(114,'Wages and Salary','Net Pay'),"
             "(115,'Wages and Salary','Overtime'),(116,'Wages and Salary','Special Payment')";
    if (!pMySql->RunQuery(Query) )
        return false;

    return true;
}

bool CreateCustomDatabase(CMySqlWrapper* pMySql, const wxString& DBName)
{
    wxString Query;

    // create the database
    Query = wxString::Format("CREATE DATABASE IF NOT EXISTS `%s`", DBName);
    if (!pMySql->RunQuery(Query) )
        return false;

    // create allowed user table
    Query = wxString::Format("CREATE TABLE IF NOT EXISTS `%s`.`tab_AllowedUsers` (", DBName);
    Query += "`col_UserName` varchar(20) COLLATE latin1_general_cs NOT NULL,"
             "PRIMARY KEY (`col_UserName`),"
             "UNIQUE KEY `col_ID_UNIQUE` (`col_UserName`)"
             ") ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs";
    if (!pMySql->RunQuery(Query) )
        return false;

    // create the reoccuring table
    Query = wxString::Format("CREATE TABLE IF NOT EXISTS `%s`.`tab_Reoccuring` (", DBName);
    Query += "`col_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,"
             "`col_Payee` varchar(25) COLLATE latin1_general_cs NOT NULL,"
             "`col_Type` varchar(15) COLLATE latin1_general_cs NOT NULL,"
             "`col_Frequency` varchar(15) COLLATE latin1_general_cs NOT NULL,"
             "`col_NextDateDue` varchar(10) COLLATE latin1_general_cs NOT NULL,"
             "`col_LastDatePaid` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,"
             "`col_NextAmountDue` decimal(10,2) NOT NULL,"
             "`col_LastAmountPaid` decimal(10,2) DEFAULT NULL,"
             "`col_AccountName` varchar(20) COLLATE latin1_general_cs NOT NULL,"
             "`col_IsDeposit` tinyint(1) NOT NULL,"
             "`col_Category` varchar(25) COLLATE latin1_general_cs DEFAULT NULL,"
             "`col_SubCategory` varchar(25) COLLATE latin1_general_cs DEFAULT NULL,"
             "PRIMARY KEY (`col_ID`),"
             "UNIQUE KEY `col_ID_UNIQUE` (`col_ID`)"
             ") ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs";
    if (!pMySql->RunQuery(Query) )
        return false;

    // create the accountlist table
    Query = wxString::Format("CREATE TABLE IF NOT EXISTS `%s`.`tab_AccountList` (", DBName);
    Query += "`col_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,"
             "`col_AccountName` varchar(20) COLLATE latin1_general_cs NOT NULL,"
             "`col_AccountNumber` varchar(20) COLLATE latin1_general_cs NOT NULL,"
             "`col_AccountType` varchar(15) COLLATE latin1_general_cs NOT NULL,"
             "`col_BankName` varchar(25) COLLATE latin1_general_cs DEFAULT NULL,"
             "`col_BankAddress` varchar(75) COLLATE latin1_general_cs DEFAULT NULL,"
             "`col_BankPhoneNumber` varchar(14) COLLATE latin1_general_cs DEFAULT NULL,"
             "`col_CurrentBalance` decimal(10,2) NOT NULL,"
             "`col_DateOpened` varchar(10) COLLATE latin1_general_cs NOT NULL,"
             "`col_OpeningBalance` decimal(10,2) NOT NULL,"
             "`col_LastStatementDate` varchar(10) COLLATE latin1_general_cs NOT NULL,"
             "`col_LastStatementBalance` decimal(10,2) NOT NULL,"
             "`col_AccountFileName` varchar(20) COLLATE latin1_general_cs NOT NULL,"
             "PRIMARY KEY (`col_ID`),"
             "UNIQUE KEY `col_id_UNIQUE` (`col_ID`)"
             ") ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs";
    if (!pMySql->RunQuery(Query) )
        return false;

    return true;
}

};
